版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/weixin_36586564/article/details/79536208

POI依赖的jar包：
<org.apache.poi.version>3.10-FINAL</org.apache.poi.version>

        <!-- POI的jar包-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${org.apache.poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${org.apache.poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${org.apache.poi.version}</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

java POI读取excel模版并填写数据代码:

public class ExportExcelDemo {
    private HSSFWorkbook workbook = null;
    /**
     * 显示的导出表的标题
     */
    private String title;
    /**
     * 导出表的列名
     */
    private String[] rowName;

    private List<Object[]> dataList = new ArrayList<>();

    /**
     * 构造方法，传入要导出的数据
     *
     * @param title
     * @param rowName
     * @param dataList
     */
    public ExportExcelDemo(String title, String[] rowName, List<Object[]> dataList) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
    }


    /**
     * 判断文件的sheet是否存在
     * @param filePath      文件路径
     * @param sheetName     表格索引名
     * @return
     */
    public boolean sheetExist(String filePath,String sheetName){
        boolean flag = false;
        File file = new File(filePath);
        if(file.exists()) {    //文件存在
            //创建workbook
            try {
                workbook = new HSSFWorkbook(new FileInputStream(file));
                //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
                HSSFSheet sheet = workbook.getSheet(sheetName);
                if(sheet!=null)
                     flag = true;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else{  //文件不存在
            flag =false;
        }
        return flag;
    }

    /**
     *
     *(2003 xls后缀 导出)
     * @param TODO
     * @return void 返回类型
     * @author xsw
     * @2016-12-7上午10:44:00
     */
    public static void createXLS(String importFilePath,String exportFilePath) throws IOException{
        try {
            //excel模板路径
            File fi=new File(importFilePath);
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
            //读取excel模板
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            //读取了模板内所有sheet内容
            HSSFSheet sheet = wb.getSheetAt(0);

            //如果这行没有了，整个公式都不会有自动计算的效果的
            sheet.setForceFormulaRecalculation(true);


            //在相应的单元格进行赋值
            HSSFCell cell = sheet.getRow(11).getCell(6);//第11行 第6列
            cell.setCellValue(1);
            HSSFCell cell2 = sheet.getRow(11).getCell(7);
            cell2.setCellValue(2);
            sheet.getRow(12).getCell(6).setCellValue(12);
            sheet.getRow(12).getCell(7).setCellValue(12);
            //修改模板内容导出新模板
            FileOutputStream out = new FileOutputStream(exportFilePath);
            wb.write(out);
            out.close();
        }catch (Exception e) {
            System.out.println("文件读取错误!");
        }

    }

    /**
     *
     *(2007 xlsx后缀 导出)
     * @param
     * @return void 返回类型
     * @author xsw
     * @2016-12-7上午10:44:30
     */
    public static void createXLSX(String importFilePath,String exportFilePath) throws IOException{
        //excel模板路径
        File fi=new File(importFilePath);
        InputStream in = new FileInputStream(fi);
        //读取excel模板
        XSSFWorkbook wb = new XSSFWorkbook(in);
        //读取了模板内所有sheet内容
        XSSFSheet sheet = wb.getSheetAt(0);

        //如果这行没有了，整个公式都不会有自动计算的效果的
        sheet.setForceFormulaRecalculation(true);


        //在相应的单元格进行赋值
        XSSFCell cell = sheet.getRow(11).getCell(6);//第12行 第7列
        cell.setCellValue(1);
        XSSFCell cell2 = sheet.getRow(11).getCell(7);
        cell2.setCellValue(2);
        sheet.getRow(12).getCell(6).setCellValue(3);
        sheet.getRow(12).getCell(7).setCellValue(4);
        //修改模板内容导出新模板
        FileOutputStream out = new FileOutputStream(exportFilePath);
        wb.write(out);
        out.close();
    }

    /**
     * @param @param  file
     * @param @return
     * @param @throws IOException
     * @return List<String> (excel每行拼接成List中的String)
     * @throws
     * @Title: readExcel
     * @Description: TODO(对外提供读取excel 的方法)
     */
    public static synchronized void readExcel(String importFilePath,String exportFilePath) throws IOException {
        File file=new File(importFilePath);
        String fileName = file.getName();
        //List<String> list = new ArrayList<String>();
        //根据其名称获取后缀
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            read2003Excel(new FileInputStream(file),exportFilePath);
        } else if ("xlsx".equals(extension) || "xlsm".equals(extension)) {
            read2007Excel(new FileInputStream(file),exportFilePath);
        } else if ("tmp".equals(extension)) {
            read2007Excel(new FileInputStream(file),exportFilePath);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    public static void main(String[] args) throws IOException{
        //excle 2007
        String importFilePath= "/Users/dataDemo.xlsx";
        String exportFilePath= "/Users/test2.xlsx";
        //createXLSX(importFilePath,exportFilePath);
        readExcel(importFilePath,exportFilePath);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162